/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.mycompany.database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author JSJSYS
 */
public class data {
    //驱动类，H2jar包中可以找到
    static final String DRIVER_CLASS = "org.h2.Driver";
    //数据库连接URL，当前连接的是E:/H2目录下的mydb数据库
    // static final String JDBC_URL = "jdbc:h2:E:/H23/mydb";
    // static final String JDBC_URL = "jdbc:h2:~/test/test";
    static final String JDBC_URL = "jdbc:h2:tcp://localhost/mem:test";
    //用户名
    static final String USER = "sa";
    //密码
    static final String PASSWORD = "12345";

    public static void main(String args[]) throws SQLException, Exception {
        try {

            // 加载驱动
            Class.forName(DRIVER_CLASS);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(data.class.getName()).log(Level.SEVERE, null, ex);
        }
        // 根据连接URL，用户名，密码获取数据库连接
        Connection conn = null;

        try {
            conn = DriverManager.getConnection(JDBC_URL, USER, PASSWORD);
        } catch (SQLException ex) {
            Logger.getLogger(data.class.getName()).log(Level.SEVERE, null, ex);
        }
        // 创建一个Statement 
        Statement stmt = null;
        try {
            stmt = conn.createStatement();
        } catch (SQLException ex) {
            Logger.getLogger(data.class.getName()).log(Level.SEVERE, null, ex);
        }
        try {
            //如果存在表就先删除
            stmt.execute("DROP TABLE IF EXISTS stu_info");
        } catch (SQLException ex) {
            Logger.getLogger(data.class.getName()).log(Level.SEVERE, null, ex);
        }
        try {
            //创建stu_info表
            stmt.execute("CREATE TABLE stu_info(id VARCHAR(36) PRIMARY KEY,name VARCHAR(100),sex VARCHAR(4),student_id VARCHAR(50))");
            //新增4 条
            stmt.executeUpdate("INSERT INTO stu_info VALUES('" + getId() + "','程凌霄','男','20151101066')");
            stmt.executeUpdate("INSERT INTO stu_info VALUES('" + getId() + "','陈秋云','女','20151101026')");
            stmt.executeUpdate("INSERT INTO stu_info VALUES('" + getId() + "','黍路','女','20151101056')");
            stmt.executeUpdate("INSERT INTO stu_info VALUES('" + getId() + "','田梦','女','20151101096')");
            p("++++++++++++++++++++++++++++++++++++++显示刚保存的四条数据：");
            show(stmt);
        } catch (SQLException ex) {
            Logger.getLogger(data.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            //释放stmt
            stmt.close();
            //关闭连接
            conn.close();
            p("------jdbc -end!!!------");
        }

    }

    static void p(String str) {
        System.out.println(str);
    }

    private static int num = 1;

    private static int getId() {
        return num++;
    }
    //遍历结果集

    private static void show(Statement stmt) throws Exception {
        //查询
        ResultSet rs = stmt.executeQuery("SELECT * FROM stu_info");
        while (rs.next()) {
            p(rs.getString(1) + "\t" + rs.getString("name") + "\t" + rs.getString("sex") + "\t" + rs.getString(4));
        }
    }
}
